package com.etoc.test.util;
 
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class CommonDB {
	protected final Log log = LogFactory.getLog(getClass());
    protected DataSource dataSource;
    
    public CommonDB(DataSource dataSource) {
		super();
		this.dataSource = dataSource;
	}

	/**
     * 获取model，即把数据库行数据map转换成pojo
     * @param rowDataMap
     * @return
     */
    //protected abstract T getModel(Map<String, Object> rowDataMap);
    
    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    protected Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
 
    /**
     * 关闭数据库连接资源
     * @param conn	
     * @param s
     * @param rs
     * @throws SQLException
     */
    protected void close(Connection conn, Statement s, ResultSet rs) throws SQLException {
        try {
			if (rs != null)
			    rs.close();
		} catch (Exception e) {
			log.error("关闭ResultSet异常", e);
			throw e;
		}
        try {
			if (s != null)
			    s.close();
		} catch (Exception e) {
			log.error("关闭Statement异常", e);
			throw e;
		}
        try {
			if (conn != null)
			    conn.close();
		} catch (Exception e) {
			log.error("关闭Connection异常", e);
			throw e;
		}
    }
    
    /**
     * 执行数据库查询语句
     * @param sql		查询sql,匿名参数用？表示，命名参数使用“：参数名”表示
     * @param params	查询参数
     * @return
     * @throws SQLException
     */
	@SuppressWarnings("unchecked")
	public List<Map<String,Object>> select(String sql, Object... params) throws SQLException{
		return (List<Map<String,Object>>)this.executeSql(sql,params);
    }
    
	/**
	 * 执行数据库记录变更语句（增，删，改）
	 * @param sql		查询sql,匿名参数用？表示，命名参数使用“：参数名”表示
	 * @param params	查询参数
	 * @return
	 * @throws SQLException
	 */
    public int update(String sql, Object... params) throws SQLException{
    	return (Integer)this.executeSql(sql,params);
    }
    
    /**
   	 * 通用Sql执行方法
   	 * @param sql		查询sql,匿名参数用？表示，命名参数使用“：参数名”表示
   	 * @param params	命名参数
   	 * @return
   	 * @throws SQLException
   	 */
    @SuppressWarnings("unchecked")
	protected Object executeSql(String sql, Object... params) throws SQLException {

    	//处理命名参数
    	if(params != null && params.length >= 1 && params[0] instanceof Map){
    		List<Object> pList=new ArrayList<Object>();
    		Map<String,Object> pMap=(Map<String, Object>)params[0];
        	Matcher pMatcher = Pattern.compile(":(\\w+)").matcher(sql);
        	while(pMatcher.find()){
        		String pName=pMatcher.group(1);
        		pList.add(pMap.get(pName));
        	}
        	sql=pMatcher.replaceAll("?");
        	params=pList.toArray();
    	}
    	
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = this.getConnection();
            ps = conn.prepareStatement(sql);
             
            if (null != params) {
                //初始化查询参数
                this.initParam(ps, params);
            }
            log.info("[SQL]: " + ps);
            //处理结果集
            boolean isResultSet = ps.execute();
            List<Object> result = new ArrayList<Object>();
            do {
                if (isResultSet) {
                	List<Map<String,Object>> tableData=new ArrayList<Map<String,Object>>();
                	ResultSet resultSet=ps.getResultSet();
                	while(resultSet.next()){
                		Map<String,Object> rowData=new HashMap<String,Object>();
                		for(int i=1;i<=resultSet.getMetaData().getColumnCount();i++){
                			//rowData.put(resultSet.getMetaData().getColumnLabel(i),resultSet.getObject(i));//可以获取别名
                			rowData.put(resultSet.getMetaData().getColumnName(i),resultSet.getObject(i));
                		}
                		tableData.add(rowData);
                	}
                    result.add(tableData);
                } else {
                    result.add(new Integer(ps.getUpdateCount()));
                }
            } while ((isResultSet = ps.getMoreResults()) == true
                    || ps.getUpdateCount() != -1);
 
            if (result.size() == 0) {
                return null;
            } else if (result.size() == 1) {
                return result.get(0);
            } else {
                return result;
            }
        } finally {
            this.close(conn, ps, rs);
        }
    }
    
    /**
     * 通过sql执行存储过程
     * @param sql			查询sql，只支持命名参数使用"：参数名"表示
     * @param inParams		in类型命名参数
     * @param outParams		out类型命名参数
     * @param inoutParams   inout类型命名参数
     * @return
     * @throws SQLException
     */
    protected int executeProcedure(String sql, Map<String, Object> inParams, Map<String, Object> outParams, Map<String, Object> inoutParams) throws SQLException {
    	Pattern pattern = Pattern.compile(":(\\w+)");
    	Map<Integer, Object> inMap = null;
    	Map<Integer, Object> outMap = null;
    	Map<Integer, Object> inoutMap = null;
    	// 处理输入命名参数
    	if(inParams != null && inParams.size() > 0){
    		inMap = new HashMap<Integer, Object>();
    		Matcher pMatcher = pattern.matcher(sql);
    		int index = 0;
        	while(pMatcher.find()){
        		index++;
        		String pName=pMatcher.group(1);
        		Object obj = inParams.get(pName);
        		if (obj != null) {
        			inMap.put(index, obj);
				}
        	}
    	}
    	// 处理输出命名参数
    	if(outParams != null && outParams.size() > 0){
    		outMap = new HashMap<Integer, Object>();
    		Matcher pMatcher = pattern.matcher(sql);
    		int index = 0;
        	while(pMatcher.find()){
        		index++;
        		String pName=pMatcher.group(1);
        		Object obj = outParams.get(pName);
        		if (obj != null) {
        			outMap.put(index, obj);
				}
        	}
    	}
    	// 处理输入输出命名参数
    	if(inoutParams != null && inoutParams.size() > 0){
    		inoutMap = new HashMap<Integer, Object>();
    		Matcher pMatcher = pattern.matcher(sql);
    		int index = 0;
        	while(pMatcher.find()){
        		index++;
        		String pName=pMatcher.group(1);
        		Object obj = inoutParams.get(pName);
        		if (obj != null) {
        			inoutMap.put(index, obj);
				}
        	}
    	}
    	if (inParams != null || outParams != null || inoutParams != null) {
    		Matcher matcher = pattern.matcher(sql);
        	sql = matcher.replaceAll("?");
		}
    	
    	Connection conn = null;
    	CallableStatement cs = null;
    	int rows = -1;
		try {
			conn = this.getConnection();
			cs = conn.prepareCall(sql);
			if (inMap != null && inMap.size() > 0) {
				initParam(cs, ParamType.IN, inMap);
			}
			if (outMap != null && outMap.size() > 0) {
				initParam(cs, ParamType.OUT, outMap);
			}
			if (inoutMap != null && inoutMap.size() > 0) {
				initParam(cs, ParamType.INTOUT, inoutMap);
			}
			log.info("[SQL]: " + cs);
			rows = cs.executeUpdate();
			return rows;
		} finally {
			this.close(conn, cs, null);
		}
    }
    
    /**
     * 通过sql执行批处理，批处理sql语句只能使用insert, update, detele
     * <功能详细描述>
     * @param sql				查询sql，只支持匿名参数使用"?"表示
     * @param params			参数数组
     * @return
     * @throws SQLException
     * @see [类、类#方法、类#成员]
     */
    protected int[] executeBatch(String sql, List<Object>[] params) throws SQLException {
    	
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int[] affectedRows = null;
        try {
            conn = this.getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            
            for (int i = 0; i < params.length; i++) {
            	List<Object> paramList = params[i];
            	if (null != paramList) {
                    //初始化查询参数
                    this.initParam(ps, paramList.toArray());
                }
            	ps.addBatch();
			} 
            log.info("[SQL]: " + ps);
            //处理结果集
            affectedRows = ps.executeBatch();
            conn.commit();
            return affectedRows;
        }catch(SQLException e) {
        	conn.rollback();
        	throw e;
        }finally {
        	try {
        		if (conn != null) {
        			conn.setAutoCommit(true);// 连接被关闭后会被连接池回收，需要手动恢复自动提交，否则后面的事务都挂起！！！
    			}
            } catch (SQLException e) {
                log.error("设置自动提交异常", e);
            }
            this.close(conn, ps, rs);
        }
    }
    
    //初始化查询参数
    private void initParam(PreparedStatement ps, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            Object param = params[i];
            if (param instanceof Byte) {
                ps.setByte(i + 1, (Byte) param);
            } else if (param instanceof Short) {
                ps.setShort(i + 1, (Short) param);
            } else if (param instanceof Integer) {
                ps.setInt(i + 1, (Integer) param);
            } else if (param instanceof Long) {
                ps.setLong(i + 1, (Long) param);
            } else if (param instanceof Float) {
                ps.setFloat(i + 1, (Float) param);
            } else if (param instanceof Double) {
                ps.setDouble(i + 1, (Double) param);
            } else if (param instanceof BigDecimal) {
                ps.setBigDecimal(i + 1, (BigDecimal) param);
            } else if (param instanceof Boolean) {
                ps.setBoolean(i + 1, (Boolean) param);
            } else if (param instanceof String) {
                ps.setString(i + 1, (String) param);
            } else if (param instanceof Time) {
                ps.setTime(i + 1, (Time) param);
            } else if (param instanceof Date) {
                ps.setDate(i + 1, (Date) param);
            } else if (param instanceof Timestamp) {
                ps.setTimestamp(i + 1, (Timestamp) param);
            } else if (param instanceof Array) {
                ps.setArray(i + 1, (Array) param);
            } else if (param instanceof Blob) {
                ps.setBlob(i + 1, (Blob) param);
            } else if (param instanceof Clob) {
                ps.setClob(i + 1, (Clob) param);
            } else if (param instanceof SQLXML) {
                ps.setSQLXML(i + 1, (SQLXML) param);
            } else if (param instanceof URL) {
                ps.setURL(i+1, (URL) param);
            } else {
                ps.setObject(i + 1, param);
            }
        }
    }
    
    //初始化参数
    private void initParam(CallableStatement cs, ParamType paramType, Map<Integer, Object> map) throws SQLException {
    	switch (paramType) {
			case IN:
				for (Map.Entry<Integer, Object> entry : map.entrySet()) {
					int index = entry.getKey();
					Object param = entry.getValue();
					if (param instanceof Byte) {
		                cs.setByte(index, (Byte) param);
		            } else if (param instanceof Short) {
		                cs.setShort(index, (Short) param);
		            } else if (param instanceof Integer) {
		                cs.setInt(index, (Integer) param);
		            } else if (param instanceof Long) {
		                cs.setLong(index, (Long) param);
		            } else if (param instanceof Float) {
		                cs.setFloat(index, (Float) param);
		            } else if (param instanceof Double) {
		                cs.setDouble(index, (double) param);
		            } else if (param instanceof BigDecimal) {
		                cs.setBigDecimal(index, (BigDecimal) param);
		            } else if (param instanceof Boolean) {
		                cs.setBoolean(index, (Boolean) param);
		            } else if (param instanceof String) {
		                cs.setString(index, (String) param);
		            } else if (param instanceof Time) {
		                cs.setTime(index, (Time) param);
		            } else if (param instanceof Date) {
		                cs.setDate(index, (Date) param);
		            } else if (param instanceof Timestamp) {
		                cs.setTimestamp(index, (Timestamp) param);
		            } else if (param instanceof Array) {
		                cs.setArray(index, (Array) param);
		            } else if (param instanceof Blob) {
		                cs.setBlob(index, (Blob) param);
		            } else if (param instanceof Clob) {
		                cs.setClob(index, (Clob) param);
		            } else if (param instanceof SQLXML) {
		                cs.setSQLXML(index, (SQLXML) param);
		            } else if (param instanceof URL) {
		                cs.setURL(index, (URL) param);
		            } else {
		                cs.setObject(index, param);
		            }
				}
				break;
			case OUT:
				for (Map.Entry<Integer, Object> entry : map.entrySet()) {
					int index = entry.getKey();
					Object param = entry.getValue();
					if (param instanceof Byte) {
						cs.registerOutParameter(index, Types.TINYINT);
		            } else if (param instanceof Short) {
		            	cs.registerOutParameter(index, Types.SMALLINT);
		            } else if (param instanceof Integer) {
		            	cs.registerOutParameter(index, Types.INTEGER);
		            } else if (param instanceof Long) {
		            	cs.registerOutParameter(index, Types.BIGINT);
		            } else if (param instanceof Float) {
		            	cs.registerOutParameter(index, Types.FLOAT);
		            } else if (param instanceof Double) {
		            	cs.registerOutParameter(index, Types.DOUBLE);
		            } else if (param instanceof BigDecimal) {
		            	cs.registerOutParameter(index, Types.DECIMAL);
		            } else if (param instanceof Boolean) {
		            	cs.registerOutParameter(index, Types.BIT);
		            } else if (param instanceof String) {
		            	cs.registerOutParameter(index, Types.VARBINARY);
		            } else if (param instanceof Time) {
		            	cs.registerOutParameter(index, Types.TIME);
		            } else if (param instanceof Date) {
		            	cs.registerOutParameter(index, Types.DATE);
		            } else if (param instanceof Timestamp) {
		            	cs.registerOutParameter(index, Types.TIMESTAMP);
		            } else if (param instanceof Array) {
		            	cs.registerOutParameter(index, Types.ARRAY);
		            } else if (param instanceof Blob) {
		            	cs.registerOutParameter(index, Types.BLOB);
		            } else if (param instanceof Clob) {
		            	cs.registerOutParameter(index, Types.CLOB);
		            } else if (param instanceof SQLXML) {
		            	cs.registerOutParameter(index, Types.SQLXML);
		            } else {
		            	cs.registerOutParameter(index, Types.JAVA_OBJECT);
		            }
				}
				break;
			case INTOUT:
				for (Map.Entry<Integer, Object> entry : map.entrySet()) {
					int index = entry.getKey();
					Object param = entry.getValue();
					if (param instanceof Byte) {
						cs.setByte(index, (Byte) param);
						cs.registerOutParameter(index, Types.TINYINT);
		            } else if (param instanceof Short) {
		            	cs.setShort(index, (Short) param);
		            	cs.registerOutParameter(index, Types.SMALLINT);
		            } else if (param instanceof Integer) {
		            	cs.setInt(index, (Integer) param);
		            	cs.registerOutParameter(index, Types.INTEGER);
		            } else if (param instanceof Long) {
		            	cs.setLong(index, (Long) param);
		            	cs.registerOutParameter(index, Types.BIGINT);
		            } else if (param instanceof Float) {
		            	cs.setFloat(index, (Float) param);
		            	cs.registerOutParameter(index, Types.FLOAT);
		            } else if (param instanceof Double) {
		            	cs.setDouble(index, (Double) param);
		            	cs.registerOutParameter(index, Types.DOUBLE);
		            } else if (param instanceof BigDecimal) {
		            	cs.setBigDecimal(index, (BigDecimal) param);
		            	cs.registerOutParameter(index, Types.DECIMAL);
		            } else if (param instanceof Boolean) {
		            	cs.setBoolean(index, (Boolean) param);
		            	cs.registerOutParameter(index, Types.BIT);
		            } else if (param instanceof String) {
		            	cs.setString(index, (String) param);
		            	cs.registerOutParameter(index, Types.VARBINARY);
		            } else if (param instanceof Time) {
		            	cs.setTime(index, (Time) param);
		            	cs.registerOutParameter(index, Types.TIME);
		            } else if (param instanceof Date) {
		            	cs.setDate(index, (Date) param);
		            	cs.registerOutParameter(index, Types.DATE);
		            } else if (param instanceof Timestamp) {
		            	cs.setTimestamp(index, (Timestamp) param);
		            	cs.registerOutParameter(index, Types.TIMESTAMP);
		            } else if (param instanceof Array) {
		            	cs.setArray(index, (Array) param);
		            	cs.registerOutParameter(index, Types.ARRAY);
		            } else if (param instanceof Blob) {
		            	cs.setBlob(index, (Blob) param);
		            	cs.registerOutParameter(index, Types.BLOB);
		            } else if (param instanceof Clob) {
		            	cs.setClob(index, (Clob) param);
		            	cs.registerOutParameter(index, Types.CLOB);
		            } else if (param instanceof SQLXML) {
		            	cs.setSQLXML(index, (SQLXML) param);
		            	cs.registerOutParameter(index, Types.SQLXML);
		            } else {
		                cs.setObject(index, param);
		                cs.registerOutParameter(index, Types.JAVA_OBJECT);
		            }
				}
				break;
		}
    }
    
    /**
     * 存储过程的参数类型
     * @author 陈超
     *
     */
    enum ParamType {
    	IN, OUT, INTOUT
    }
    
    public static void main(String[] args) throws Exception {
    	BasicDataSource ds = new BasicDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");  
        ds.setUsername("root");  
        ds.setPassword("root");  
        ds.setUrl("jdbc:mysql://127.0.0.1:3306/test");  
        ds.setInitialSize(8);
        ds.setMaxTotal(10);  
        ds.setMaxIdle(5);  
        ds.setMinIdle(2);
        ds.setMaxWaitMillis(30000); 
        
    	CommonDB db = new CommonDB(ds);
    	String sql = "select trim(m.id) `主键`, m.text `内容` from mima m";
    	List<Map<String, Object>> list = db.select(sql);
    	System.out.println(list);
	}
}